package Presentacion;

import AccesoDatos.Comando;
import AccesoDatos.Conexion;
import LogicaNegocio.Cliente;
import java.awt.Dimension;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author REYES
 */
public class frmAdminCartera extends javax.swing.JInternalFrame {
    Conexion conexion;
    Vector <Cliente> vClientes = new Vector <Cliente>();
    
    public frmAdminCartera(Conexion Pconexion) {
        conexion = Pconexion;
        initComponents();
        cargarClientes();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        lbClientes = new javax.swing.JLabel();
        cbCliente = new javax.swing.JComboBox();
        btnCargaCartera = new javax.swing.JButton();
        btnInfoContacto = new javax.swing.JButton();
        btnSalir = new javax.swing.JButton();
        lbCargaCartera = new javax.swing.JLabel();

        setClosable(true);
        setIconifiable(true);
        setTitle("Gestión de Cartera");

        lbClientes.setFont(new java.awt.Font("Tahoma", 0, 14)); // NOI18N
        lbClientes.setText("Seleccione el cliente:");
        lbClientes.setToolTipText("");

        cbCliente.setFont(new java.awt.Font("Tahoma", 0, 14)); // NOI18N

        btnCargaCartera.setFont(new java.awt.Font("Tahoma", 0, 14)); // NOI18N
        btnCargaCartera.setText("Carga Inicial de Cartera");
        btnCargaCartera.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnCargaCarteraActionPerformed(evt);
            }
        });

        btnInfoContacto.setFont(new java.awt.Font("Tahoma", 0, 14)); // NOI18N
        btnInfoContacto.setText("Información de contacto");
        btnInfoContacto.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnInfoContactoActionPerformed(evt);
            }
        });

        btnSalir.setFont(new java.awt.Font("Tahoma", 0, 14)); // NOI18N
        btnSalir.setIcon(new javax.swing.ImageIcon(getClass().getResource("/src/imagenes/salir_login.png"))); // NOI18N
        btnSalir.setText("Salir");
        btnSalir.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnSalirActionPerformed(evt);
            }
        });

        lbCargaCartera.setFont(new java.awt.Font("Tahoma", 0, 14)); // NOI18N
        lbCargaCartera.setText("Elija el tipo de carga que desea realizar:");

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                        .addComponent(btnSalir, javax.swing.GroupLayout.PREFERRED_SIZE, 95, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(lbClientes)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                .addComponent(cbCliente, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addGap(159, 159, 159))
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(btnInfoContacto)
                                .addGap(39, 39, 39)
                                .addComponent(btnCargaCartera))))
                    .addComponent(lbCargaCartera))
                .addContainerGap(27, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lbClientes)
                    .addComponent(cbCliente, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addComponent(lbCargaCartera)
                .addGap(16, 16, 16)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(btnCargaCartera, javax.swing.GroupLayout.PREFERRED_SIZE, 84, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(btnInfoContacto, javax.swing.GroupLayout.PREFERRED_SIZE, 84, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addComponent(btnSalir, javax.swing.GroupLayout.DEFAULT_SIZE, 56, Short.MAX_VALUE)
                .addGap(26, 26, 26))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents
    private void cargarClientes(){
            vClientes.clear();
    try{
            DefaultComboBoxModel modeloCombo = new DefaultComboBoxModel();
            int i = 1;            
            Cliente cliente1 = new Cliente(); //Creamos un cliente con los atributos vacios, para la posicion 1 del ComboBox(Agregar nuevo)
            vClientes.add(0, cliente1);       // Agregamos el cliente en la primera posicion
            modeloCombo.addElement("...");
            Comando comando = new Comando("select * from tb_cliente");
            ResultSet rs = comando.ejecutarSQL(conexion); 
            while (rs.next())
            {
              Cliente cliente = new Cliente();
              vClientes.add(i, cliente);
              vClientes.elementAt(i).setId_Cliente(rs.getInt("Id_Cliente"));
              vClientes.elementAt(i).setCedula_Juridica(rs.getString("Cedula_Juridica"));
              vClientes.elementAt(i).setNombre_Empresa(rs.getString("Nombre_Empresa"));
              vClientes.elementAt(i).setNombre_Encargado(rs.getString("Nombre_Encargado"));
              vClientes.elementAt(i).setTelefono(rs.getString("Telefono"));
              vClientes.elementAt(i).setDireccion(rs.getString("Direccion"));
              vClientes.elementAt(i).setFax(rs.getString("Fax"));
              vClientes.elementAt(i).setEmail(rs.getString("Email"));
              modeloCombo.addElement(rs.getString("Nombre_Empresa"));
              i++;
            }
            cbCliente.setModel(modeloCombo);
        }catch(Exception ex){
            JOptionPane.showMessageDialog(null, ex.getMessage(), null, JOptionPane.ERROR_MESSAGE);
        }
    
    }
    private void btnSalirActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnSalirActionPerformed
        this.dispose();
    }//GEN-LAST:event_btnSalirActionPerformed

    private void btnInfoContactoActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnInfoContactoActionPerformed
        Vector headers = new Vector();
        Vector data = new Vector();
        //
        JFileChooser fileChooser = new JFileChooser(".");
        int status = fileChooser.showOpenDialog(null); //fileChooser

        if (status == JFileChooser.APPROVE_OPTION) { 
            //Carga del excel e inclusion en la bd
            File selectedFile =fileChooser.getSelectedFile();
            String direccion=selectedFile.getParent()+"/"+selectedFile.getName();

            //
            File file = new File(direccion);
            
            try {
            WorkbookSettings ws = new WorkbookSettings();
            ws.setEncoding("Cp1252");
            Workbook workbook = Workbook.getWorkbook(file, ws);
            Sheet sheet = workbook.getSheet(0);
            headers.clear();
            for (int i = 0; i < sheet.getColumns(); i++) {
            Cell cell1 = sheet.getCell(i, 0);
            headers.add(cell1.getContents());
            }
            data.clear();
            for (int j = 1; j < sheet.getRows(); j++) {
            Vector d = new Vector();
            for (int i = 0; i < sheet.getColumns(); i++) {
            Cell cell = sheet.getCell(i, j);
            String cedula = sheet.getCell(0, j).getContents();
            if(i==0){
                try{ // Insert de los contactos
                        Comando comando = new Comando();
                        comando.setConsulta("INSERT INTO `sicgsadb`.`tb_info_contacto` (`Id_Info_Contacto`) VALUES ('"+ cell.getContents() +"')");
                        comando.updateSQL(conexion);
                            //JOptionPane.showMessageDialog(this, "El cliente se ingresó exitosamente", "Información", JOptionPane.INFORMATION_MESSAGE);

                    }catch(Exception ex){
                        JOptionPane.showMessageDialog(null, ex.getMessage(), null, JOptionPane.ERROR_MESSAGE);
                        //System.out.println(ex.getMessage());
                    }
            }else{
                if(i==2 || i==3 || i==4 || i==5 || i==6 || i==7 || i==8 || i==9){
                    if(!cell.getContents().isEmpty()){
                            try{ // Insert de los telefonos
                                Comando comando = new Comando();
                                comando.setConsulta("INSERT INTO `sicgsadb`.`tb_telefono_deudor` (`Id_Info_Contacto`, `Numero_Telefono`) VALUES ('"+ cedula +"', '"+ cell.getContents() +"')");
                                comando.updateSQL(conexion);
                                    //JOptionPane.showMessageDialog(this, "El cliente se ingresó exitosamente", "Información", JOptionPane.INFORMATION_MESSAGE);

                            }catch(Exception ex){
                                JOptionPane.showMessageDialog(null, ex.getMessage(), null, JOptionPane.ERROR_MESSAGE);
                                //System.out.println(ex.getMessage());
                            }
                    }
                }else{
                    switch(i){
                        case 10:                                                           
                        case 14:
                        case 18:
                        case 22:
                        case 26:
                        case 30:
                        case 34:
                        case 38:
                                if(sheet.getCell(i, j).getContents().isEmpty() && sheet.getCell(i+1, j).getContents().isEmpty() && sheet.getCell(i+2, j).getContents().isEmpty() && sheet.getCell(i+3, j).getContents().isEmpty()){
                                    break;
                                }else{// Insert de la direccion
                                    try{
                                    //System.out.println("Dato: " + sheet.getCell(i, j).getContents());
                                    Comando comando = new Comando();
                                    comando.setConsulta("INSERT INTO `sicgsadb`.`tb_direccion_deudor` (`Id_Info_Contacto`, `Provincia`, `Canton`, `Distrito`, `Señas`) VALUES ('"+ cedula +"', '"+ sheet.getCell(i, j).getContents() +"', '"+ sheet.getCell(i+1, j).getContents() +"', '"+ sheet.getCell(i+2, j).getContents() +"', '"+ sheet.getCell(i+3, j).getContents() +"')");
                                    comando.updateSQL(conexion);
                                        //JOptionPane.showMessageDialog(this, "El cliente se ingresó exitosamente", "Información", JOptionPane.INFORMATION_MESSAGE);

                                    }catch(Exception ex){
                                        JOptionPane.showMessageDialog(null, ex.getMessage(), null, JOptionPane.ERROR_MESSAGE);
                                        //System.out.println(ex.getMessage());
                                    }
                                }
                            break;
                    
                    }
                   
                   
                }
            
            }               
            d.add(cell.getContents());
            }
            d.add("\n");
            data.add(d);
            }
            JOptionPane.showMessageDialog(this, "La información se cargó exitosamente", "Información", JOptionPane.INFORMATION_MESSAGE);
            }
            catch (Exception e) {
            e.printStackTrace();
            }
        }else if (status == JFileChooser.CANCEL_OPTION) {

            System.out.println("cancele");

        }       
    }//GEN-LAST:event_btnInfoContactoActionPerformed
    private boolean InsertarDeudor(String cedula, String nombre){
               try{             
                    Comando comando1 = new Comando("SELECT Id_Info_Contacto from tb_info_contacto where Id_Info_Contacto = '"+ cedula +"'");
                    ResultSet rs1 = comando1.ejecutarSQL(conexion); 
                if(!rs1.next()){
                    Comando comando = new Comando();       
                    comando.setConsulta("INSERT INTO `sicgsadb`.`tb_deudor` (`Id_Deudor`, `Nombre`) VALUES ('"+ cedula +"', '"+ nombre +"')");
                    if(!comando.updateSQL(conexion)){
                        return false;
                    }
                    
                }else{
                    Comando comando = new Comando();       
                    comando.setConsulta("INSERT INTO `sicgsadb`.`tb_deudor` (`Id_Deudor`, `Id_Info_Contacto`, `Nombre`) VALUES ('"+ cedula +"', '"+ cedula +"', '"+ nombre +"')");
                    if(!comando.updateSQL(conexion)){
                        return false;
                    }
                        
                }
                
            }catch(Exception ex){      
                JOptionPane.showMessageDialog(null, ex.getMessage(), null, JOptionPane.ERROR_MESSAGE);
                //System.out.println(ex.getMessage());
            } 
               return true;
    }
    private boolean InsertarDeuda(String[] Deuda){
    
        try{      
                //String dateString1 = "16-04-2011";
                Date date1 = new SimpleDateFormat("dd/MM/yyyy").parse(Deuda[16]);
                String FechaUltimoPago = new SimpleDateFormat("yyyy/MM/dd").format(date1);
                Date date2 = new SimpleDateFormat("dd/MM/yyyy").parse(Deuda[17]);
                String FechaProximoPago = new SimpleDateFormat("yyyy/MM/dd").format(date2);
                
                Comando comando = new Comando();                
                comando.setConsulta("INSERT INTO `sicgsadb`.`tb_deuda` (`Id_Deuda`, `Id_Deudor`, `Id_Cartera`, `Id_Usuario`, `Producto`, `Dias_Mora`, `Categoria`, `Capital_Adeudado`, `Cuota`, `Saldo_Colones`, `Saldo_Dolares`, `Total_Adeudado_Colones`, `Total_Adeudado_Dolares`, `Saldo_Mora_Colones`, `Saldo_Mora_Dolares`, `Pago_Minimo_Colones`, `Pago_Minimo_Dolares`, `Fecha_Ultimo_Pago`, `Fecha_Proximo_Pago`, `Monto_Pago`) VALUES ('"+ Deuda[3] +"', '"+ Deuda[1] +"', '1', '1', '"+ Deuda[0] +"', '"+ Deuda[4] +"', '"+ Deuda[5] +"', '"+ Deuda[6] +"', '"+ Deuda[7] +"', '"+ Deuda[8] +"', '"+ Deuda[9] +"', '"+ Deuda[10] +"', '"+ Deuda[11] +"', '"+ Deuda[12] +"', '"+ Deuda[13] +"', '"+ Deuda[14] +"', '"+ Deuda[15] +"', '"+ FechaUltimoPago +"', '"+ FechaProximoPago +"', '"+ Deuda[18] +"')");
                comando.updateSQL(conexion);                 
                                                             
            }catch(Exception ex){
                JOptionPane.showMessageDialog(null, ex.getMessage(), null, JOptionPane.ERROR_MESSAGE);
                //System.out.println(ex.getMessage());
            }
        return true;
    
    }
    private void btnCargaCarteraActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnCargaCarteraActionPerformed

        JFileChooser fileChooser = new JFileChooser(".");
        int status = fileChooser.showOpenDialog(null); //fileChooser

        if (status == JFileChooser.APPROVE_OPTION) { 
                File selectedFile =fileChooser.getSelectedFile();
                String direccion=selectedFile.getParent()+"/"+selectedFile.getName();
                DefaultTableModel myModel = new DefaultTableModel();
                myModel.addColumn("Produc");
                myModel.addColumn("Cedula");
                myModel.addColumn("Nombre");
                myModel.addColumn("Oper");
                myModel.addColumn("DiasMora");
                myModel.addColumn("Cat");
                myModel.addColumn("Cap_Adeu");
                myModel.addColumn("Cuota");
                myModel.addColumn("Sal_Con_Col");
                myModel.addColumn("Sal_Con_Dol");
                myModel.addColumn("Tot_Adeu_Col");
                myModel.addColumn("Tot_Adeu_Dol");
                myModel.addColumn("Sal_Mora_Col");
                myModel.addColumn("Sal_Mora_Dol");
                myModel.addColumn("Pago_Min_Col");
                myModel.addColumn("Pago_Min_Dol");
                myModel.addColumn("Ult_Pago");
                myModel.addColumn("Prox_Pago");
                myModel.addColumn("Monto_Pago");

                JTable table = new JTable(myModel);
                //table.setPreferredScrollableViewportSize(new Dimension(1200, 700));
                Vector cedulasDuplicadas = new Vector(); //Vector para guardar las cedulas de los deudores con mas de 1 operacion
                    BufferedReader br = null;
                        try{                    
                            // new FileReader("C:/GUATETICOS1404.txt")
                            br = new BufferedReader(new InputStreamReader(new FileInputStream(direccion), "Cp1252"));
                            String line = br.readLine();

                            for(int row = 0; row < 10 ; row++){
                                for(int column = 0; column<5 ;column++){
                                           int i=0;
                                             line = br.readLine();
                                           while (line != null )
                                           {
                                             String delims = "[|]";
                                             String [] rowfields = line.split(delims);                                             
                                             if(!InsertarDeudor(rowfields[1], rowfields[2])){
                                                 cedulasDuplicadas.add(rowfields[1]);
                                                 System.out.println(rowfields[1]);
                                             }
                                             myModel.addRow(rowfields);
                                             InsertarDeuda(rowfields);
                                             line = br.readLine();
                                            }

                                        }
                                     }
                                        //Aqui ya esta la tabla con la info cargada
                            JOptionPane.showMessageDialog(this, "La carga se realizó correctamente", "Información", JOptionPane.INFORMATION_MESSAGE);
                                }
                        catch(Exception e)
                          {
                                    System.out.println(e.getMessage());
                          }
                   
         }else if (status == JFileChooser.CANCEL_OPTION) {

            System.out.println("cancele");

         } 
    }//GEN-LAST:event_btnCargaCarteraActionPerformed

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnCargaCartera;
    private javax.swing.JButton btnInfoContacto;
    private javax.swing.JButton btnSalir;
    private javax.swing.JComboBox cbCliente;
    private javax.swing.JLabel lbCargaCartera;
    private javax.swing.JLabel lbClientes;
    // End of variables declaration//GEN-END:variables
}
